﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using TpBaseObjects.Models.BusinessCases;
using TpBaseObjects.Objects;
using TpContracts;
using TpData.Manager;

namespace TpData.Layer {

    internal class BusinessCaseDataLayer : BaseSqlDataLayer {

        #region Members

        private const string loadBusinessCaseStatement = @"Projects.GetBusinessCases";
        private const string loadBusinessCaseShortListStatement = @"Projects.GetBusinessCasesShortList";
        private const string saveBusinessCaseStatement = @"Projects.SaveProject";
        private const string loadBusinessCaseStatusStatement = @"Projects.GetBusinessCaseStatusList";
        private const string loadCustomerSourceStatement = @"dbo.GetCustomerSource";
        private const string updateBusinessCaseStatement = @"Projects.UpdateProjectValues";
        private const string GetPurchaserProjectCountStatement = @"Projects.GetPurchaserProjectCount";
        private const string GetPurchaserNamesCountStatement = @"Projects.GetPurchaserNamesCount";
        private const string logArea = "TpData.Layer.BusinessCaseDataLayer";
        private const string errType = "Err";
        private const string logType = "Log";

        #endregion

        #region Load

        public virtual int GetPurchaserNamesCount(int purchaserID, int year) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(GetPurchaserNamesCountStatement, 
                new SqlParameter("@OrgKey", purchaserID),
                new SqlParameter("@Year", year))) {
                    if (reader.Read()) {
                        return this.GetInt32(reader, "NamesCount");
                    }
                    return 0;
            }
        }

        public virtual int GetPurchaserProjectCount(int purchaserID, int year) {
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(GetPurchaserProjectCountStatement,
                new SqlParameter("@PurchaserID", purchaserID),
                new SqlParameter("@Year", year))) {
                if (reader.Read()) {
                    return this.GetInt32(reader, "ProjectsCount");
                }
                return 0;
            }
        }

        public virtual BusinessCaseModel LoadBusinessCase(int projectID) {
            BusinessCaseModel bcm = new BusinessCaseModel();
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadBusinessCaseStatement, new SqlParameter("@ProjectID", projectID))) {
                bcm = (reader.Read() ? LoadBusinessCaseFromDataReader(reader) : null);
            }
            return bcm;
        }

        public virtual List<BusinessCasesShortListModel> LoadBusinessCasesShortList(SqlParameter[] parameters) {
            List<BusinessCasesShortListModel> retList = new List<BusinessCasesShortListModel>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(loadBusinessCaseShortListStatement, parameters)) {
                foreach (DataRow dr in dt.Rows) {
                    BusinessCasesShortListModel bcm = new BusinessCasesShortListModel();
                    try {
                        bcm.ProjectID = (int)dr["ProjectID"];
                        if (Convert.ToString(dr["CostSummary"]) != "") {
                            bcm.CostSummary = Convert.ToDouble(dr["CostSummary"]);
                        }
                        bcm.CreatedBy = (string)dr["CreatedBy"];
                        if (Convert.ToString(dr["CreatedOn"]) != "") {
                            bcm.CreatedOn = (DateTime)dr["CreatedOn"];
                        }
                        bcm.CustomerGroup = (byte)dr["CustomerGroup"];
                        bcm.CustomerSource = (string)dr["CustomerSource"];
                        if (Convert.ToString(dr["ErloesSumNetto"]) != "") {
                            bcm.ErloesSumNetto = Convert.ToDouble(dr["ErloesSumNetto"]);
                        }
                        bcm.FileUser = (string)dr["FileUser"];
                        if (Convert.ToString(dr["FinishDate"]) != "") {
                            bcm.FinishDate = (DateTime)dr["FinishDate"];
                        }
                        if (Convert.ToString(dr["FirstContactDate"]) != "") {
                            bcm.FirstContactDate = (DateTime)dr["FirstContactDate"];
                        }
                        if (Convert.ToString(dr["ImplementationDate"]) != "") {
                            bcm.ImplementationDate = (DateTime)dr["ImplementationDate"];
                        }
                        bcm.Intern = (bool)dr["Intern"];
                        bcm.InvoiceRecipient = (string)dr["InvoiceRecipient"];
                        bcm.LastChangedBy = (string)dr["LastChangedBy"];
                        if (Convert.ToString(dr["LastChangedOn"]) != "") {
                            bcm.LastChangedOn = (DateTime)dr["LastChangedOn"];
                        }
                        if (Convert.ToString(dr["OrderDate"]) != "") {
                            bcm.OrderDate = (DateTime)dr["OrderDate"];
                        }
                        bcm.ProjectAddress = (string)dr["ProjectAddress"];
                        bcm.ProjectStatusText = (string)dr["ProjectStatusText"];
                        if (Convert.ToString(dr["ProjectStatus"]) != "") {
                            bcm.ProjectStatus = Convert.ToInt32(dr["ProjectStatus"]);
                        }
                        bcm.Purchaser = (string)dr["Purchaser"];
                        if (Convert.ToString(dr["ResultDB"]) != "") {
                            bcm.ResultDB = Convert.ToDouble(dr["ResultDB"]);
                        }
                        bcm.ShortName = (string)dr["ShortName"];
                        if (Convert.ToString(dr["TimeSummary"]) != "") {
                            bcm.TimeSummary = Convert.ToDouble(dr["TimeSummary"]);
                        }
                        retList.Add(bcm);
                    } catch (Exception ex) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "LoadBusinessCase: " + ex.Message, true);
                    }
                }
            }
            return retList;
        }

        public virtual BusinessCaseStatusObj LoadBusinessCaseStatus(byte status) {
            BusinessCaseStatusObj ret = new BusinessCaseStatusObj();
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadBusinessCaseStatusStatement, new SqlParameter("@Status", status))) {
                ret = (reader.Read() ? LoadBusinessCaseStatusFromDataReader(reader) : null);
            }
            return ret;
        }

        public virtual List<BusinessCaseStatusObj> LoadBusinessCaseStatusList() {
            List<BusinessCaseStatusObj> ret = new List<BusinessCaseStatusObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(loadBusinessCaseStatusStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    BusinessCaseStatusObj bcs = new BusinessCaseStatusObj();
                    bcs.ProjectStatus = (byte)dr["ProjectStatus"];
                    bcs.StateText = (string)dr["StateText"];
                    bcs.FilePath = (string)dr["FilePath"];
                    ret.Add(bcs);
                }
            }
            return ret;
        }

        public virtual CustomerSourceObj LoadCustomerSource(int customerSourceID) {
            CustomerSourceObj ret = new CustomerSourceObj();
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadCustomerSourceStatement, new SqlParameter("@CustomerSourceID", customerSourceID))) {
                ret = (reader.Read() ? LoadCustomerSourceFromDataReader(reader) : null);
            }
            return ret;
        }

        public virtual List<CustomerSourceObj> LoadCustomerSourceList() {
            List<CustomerSourceObj> ret = new List<CustomerSourceObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(loadCustomerSourceStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    CustomerSourceObj cs = new CustomerSourceObj();
                    cs.CustomerSourceID = (int)dr["CustomerSourceID"];
                    cs.CustomerSourceLong = (string)dr["CustomerSourceLong"];
                    cs.CustomerSourceShort = (string)dr["CustomerSourceShort"];
                    ret.Add(cs);
                }
            }
            return ret;
        }

        #endregion

        #region Save

        public virtual void SaveBusinessCase(BusinessCaseModel model) {
            SqlParameter[] parameters = GetParameters(model);
            int ret = 0;
            ret = this.ExecuteStoredProcedure(saveBusinessCaseStatement, parameters);
            if (ret != 1) {
                //toDo: Error
            }
        }

        #endregion

        #region Update

        public virtual void UpdateBusinessCase(BusinessCaseModel model) {
            SqlParameter[] parameters = GetParameters(model, true);
            int ret = 0;
            ret = this.ExecuteStoredProcedure(updateBusinessCaseStatement, parameters);
            if (ret != 1) {
                //toDo: Error
            }
        }

        #endregion

        #region Methods
        #endregion

        #region Functions

        private BusinessCaseModel LoadBusinessCaseFromDataReader(IDataReader reader) {
            BusinessCaseModel bcm = new BusinessCaseModel();

            //BusinessCase
            bcm.ProjectID = this.GetInt32(reader, "ProjectID");
            bcm.CalculationUserID = this.GetInt32(reader, "CalculationUserID");
            bcm.CreatedBy = this.GetString(reader, "CreateBy");
            bcm.CreatedOn = this.GetDateTime(reader, "CreateOn");
            bcm.CustomerSource = this.GetInt32(reader, "CustomerSource");
            bcm.FileUserID = this.GetInt32(reader, "FileUserID");
            bcm.FilePath = this.GetString(reader, "FilePath");
            bcm.FinishDate = (DateTime)this.GetDateTime(reader, "FinishDate");
            bcm.FirstContactDate = (DateTime)this.GetDateTime(reader, "FirstContactDate");
            bcm.FirstContactUserID = this.GetInt32(reader, "FirstContactUserID");
            bcm.ImplementationDate = (DateTime)this.GetDateTime(reader, "ImplementationDate");
            bcm.OrderDate = this.GetDateTime(reader, "OrderDate");
            bcm.ProjectAddress = this.GetString(reader, "Address");
            bcm.ProjectEPLZ = this.GetInt32(reader, "EPLZ");
            bcm.ProjectStatus = this.GetByte(reader, "ProjectStatus");
            bcm.Purchaser = this.GetInt32(reader, "PurchaserID");
            bcm.InvoiceRecipient = this.GetInt32(reader, "InvoiceToID");
            bcm.SalesUserID = this.GetInt32(reader, "SalesUserID");
            bcm.ShortName = this.GetString(reader, "ShortName");
            bcm.Distance = (double)this.GetDecimal(reader, "Distance");
            bcm.Saved = this.GetBoolean(reader, "Saved");

            //Attributes
            bcm.AaEf = this.GetByte(reader, "AaEf");
            bcm.AaEi = this.GetByte(reader, "AaEi");
            bcm.AaGe = this.GetByte(reader, "AaGe");
            bcm.AaZa = this.GetByte(reader, "AaZa");
            bcm.AnToFl = this.GetByte(reader, "AnToFl");
            bcm.AnToSeRo = this.GetByte(reader, "AnToSeRo");
            bcm.AnToSi = this.GetByte(reader, "AnToSi");
            bcm.AnToSr = this.GetByte(reader, "AnToSr");
            bcm.Carport = this.GetByte(reader, "Carport");
            bcm.EiToFl = this.GetByte(reader, "EiToFl");
            bcm.EiToSi = this.GetByte(reader, "EiToSi");
            bcm.GaToAl = (byte)this.GetByte(reader, "GaToAl");
            bcm.GaToDi = this.GetByte(reader, "GaToDi");
            bcm.GaToHm = this.GetByte(reader, "GaToHm");
            bcm.LeBl = this.GetByte(reader, "LeBl");
            bcm.LeBlAverage = (double)this.GetDecimal(reader, "LeBlAverage");
            bcm.LeBr = this.GetByte(reader, "LeBr");
            bcm.LeBrAverage = (double)this.GetDecimal(reader, "LeBrAverage");
            bcm.LeDe = this.GetByte(reader, "LeDe");
            bcm.LeMo = this.GetByte(reader, "LeMo");
            bcm.LeRe = this.GetByte(reader, "LeRe");
            bcm.LeReAverage = (double)this.GetDecimal(reader, "LeReAverage");
            bcm.LeUk = this.GetByte(reader, "LeUk");
            bcm.SbGa = this.GetByte(reader, "SbGa");
            bcm.SbGh = this.GetByte(reader, "SbGh");
            bcm.SbHs = this.GetByte(reader, "SbHs");
            bcm.SbHsAverage = (double)this.GetDecimal(reader, "LeBlAverage");

            //Values
            bcm.CostAdministration = (double)this.GetDecimal(reader, "CostAdministration");
            bcm.CostHandelsware = (double)this.GetDecimal(reader, "CostHandelsware");
            bcm.CostMontage = (double)this.GetDecimal(reader, "CostMontage");
            bcm.CostProduktion = (double)this.GetDecimal(reader, "CostProduktion");
            bcm.ErloesEm = (double)this.GetDecimal(reader, "ErloesEm");
            bcm.ErloesFa = (double)this.GetDecimal(reader, "ErloesFa");
            bcm.ErloesRaLe = (double)this.GetDecimal(reader, "ErloesRaLe");
            bcm.ErloesRaPr = (double)this.GetDecimal(reader, "ErloesRaPr");
            bcm.ErloesRaPro = (double)this.GetDecimal(reader, "ErloesRaPro");
            bcm.ErloesSf = (double)this.GetDecimal(reader, "ErloesSf");
            bcm.ErloesSuHandelsware = (double)this.GetDecimal(reader, "ErloesSuHw");
            bcm.ErloesSuMontage = (double)this.GetDecimal(reader, "ErloesSuMo");
            bcm.ErloesSuProduktion = (double)this.GetDecimal(reader, "ErloesSuPr");
            bcm.ExtraCostBIFix = (double)this.GetDecimal(reader, "ExtraCostBIFix");
            bcm.ExtraCostBIPercent = (double)this.GetDecimal(reader, "ExtraCostBIPercent");
            bcm.ExtraCostErfGar = (double)this.GetDecimal(reader, "ExtraCostErfGar");
            bcm.ExtraCostHaftRuecklage = (double)this.GetDecimal(reader, "ExtraCostHaftRuecklage");
            bcm.ExtraCostPrEFix = (double)this.GetDecimal(reader, "ExtraCostPrEFix");
            bcm.ExtraCostPrEPercent = (double)this.GetDecimal(reader, "ExtraCostPrEPercent");
            bcm.ExtraCostProvisionDB = (double)this.GetDecimal(reader, "ExtraCostProvisionDB");
            bcm.ExtraCostSkonto = (double)this.GetDecimal(reader, "ExtraCostSkonto");
            bcm.TimeAdministration = (double)this.GetDecimal(reader, "TimeAdministration");
            bcm.TimeMontage = (double)this.GetDecimal(reader, "TimeMontage");
            bcm.TimeProduktion = (double)this.GetDecimal(reader, "TimeProduktion");

            return bcm;
        }

        private BusinessCaseStatusObj LoadBusinessCaseStatusFromDataReader(IDataReader reader) {
            BusinessCaseStatusObj ret = new BusinessCaseStatusObj();
            ret.ProjectStatus = this.GetByte(reader, "ProjectStatus");
            ret.StateText = this.GetString(reader, "StateText");
            ret.FilePath = this.GetString(reader, "FilePath");
            return ret;
        }

        private CustomerSourceObj LoadCustomerSourceFromDataReader(IDataReader reader) {
            CustomerSourceObj ret = new CustomerSourceObj();
            ret.CustomerSourceID = this.GetInt32(reader, "CustomerSourceID");
            ret.CustomerSourceLong = this.GetString(reader, "CustomerSourceLong");
            ret.CustomerSourceShort = this.GetString(reader, "CustomerSourceShort");
            return ret;
        }

        protected virtual SqlParameter[] GetParameters(BusinessCaseModel model) {
            SqlParameter[] ps = new SqlParameter[74];

            //BusinessCase
            ps[0] = new SqlParameter("@ProjectID", model.ProjectID);
            ps[1] = new SqlParameter("@Intern", model.Intern);
            ps[2] = new SqlParameter("@ShortName", model.ShortName);
            ps[3] = new SqlParameter("@PurchaserID", model.Purchaser);
            ps[4] = new SqlParameter("@FirstContactDate", model.FirstContactDate);
            ps[5] = new SqlParameter("@FirstContactUserID", model.FirstContactUserID);
            ps[6] = new SqlParameter("@Address", model.ProjectAddress);
            ps[7] = new SqlParameter("@EPLZ", model.ProjectEPLZ);
            ps[8] = new SqlParameter("@ImplementationDate", model.ImplementationDate);
            ps[9] = new SqlParameter("@CustomerSource", model.CustomerSource);
            ps[10] = new SqlParameter("@CustomerGroup", model.CustomerGroup);
            ps[11] = new SqlParameter("@CalculationUserID", model.CalculationUserID);
            ps[12] = new SqlParameter("@SalesUserID", model.SalesUserID);
            ps[13] = new SqlParameter("@FileUserID", model.FileUserID);
            ps[14] = new SqlParameter("@UserID", Contracts.MyUser.UserID);
            ps[15] = new SqlParameter("@ProjectStatus", model.ProjectStatus);
            ps[16] = new SqlParameter("@OrderDate", model.OrderDate);
            ps[17] = new SqlParameter("@FinishDate", model.FinishDate);
            ps[18] = new SqlParameter("@FilePath", model.FilePath);

            //Attributes
            ps[19] = new SqlParameter("@GaToHm", model.GaToHm);
            ps[20] = new SqlParameter("@GaToAl", model.GaToAl);
            ps[21] = new SqlParameter("@GaToDi", model.GaToDi);
            ps[22] = new SqlParameter("@EiToSi", model.EiToSi);
            ps[23] = new SqlParameter("@EiToFl", model.EiToFl);
            ps[24] = new SqlParameter("@AnToSeRo", model.AnToSeRo);
            ps[25] = new SqlParameter("@AnToSi", model.AnToSi);
            ps[26] = new SqlParameter("@AnToFl", model.AnToFl);
            ps[27] = new SqlParameter("@AnToSr", model.AnToSr);
            ps[28] = new SqlParameter("@SbGa", model.SbGa);
            ps[29] = new SqlParameter("@SbGh", model.SbGh);
            ps[30] = new SqlParameter("@SbHs", model.SbHs);
            ps[31] = new SqlParameter("@SbHsAverage", model.SbHsAverage);
            ps[32] = new SqlParameter("@AaEi", model.AaEi);
            ps[34] = new SqlParameter("@AaZa", model.AaZa);
            ps[35] = new SqlParameter("@AaGe", model.AaGe);
            ps[36] = new SqlParameter("@AaEf", model.AaEf);
            ps[37] = new SqlParameter("@Carport", model.Carport);
            ps[38] = new SqlParameter("@LeDe", model.LeDe);
            ps[39] = new SqlParameter("@LeMo", model.LeMo);
            ps[40] = new SqlParameter("@LeUk", model.LeUk);
            ps[41] = new SqlParameter("@LeBr", model.LeBr);
            ps[42] = new SqlParameter("@LeBrAverage", model.LeBrAverage);
            ps[43] = new SqlParameter("@LeRe", model.LeRe);
            ps[44] = new SqlParameter("@LeReAverage", model.LeReAverage);
            ps[45] = new SqlParameter("@LeBl", model.LeBl);
            ps[46] = new SqlParameter("@LeBlAverage", model.LeBlAverage);

            //Values
            ps[47] = new SqlParameter("@ErloesSuHw", model.ErloesSuHandelsware);
            ps[48] = new SqlParameter("@ErloesSuPr", model.ErloesSuProduktion);
            ps[49] = new SqlParameter("@ErloesSuMo", model.ErloesSuMontage);
            ps[50] = new SqlParameter("@ErloesRaPr", model.ErloesRaPr);
            ps[51] = new SqlParameter("@ErloesRaLe", model.ErloesRaLe);
            ps[52] = new SqlParameter("@ErloesRaPro", model.ErloesRaPro);
            ps[53] = new SqlParameter("@ErloesSf", model.ErloesSf);
            ps[54] = new SqlParameter("@ErloesEm", model.ErloesEm);
            ps[55] = new SqlParameter("@ErloesFa", model.ErloesFa);
            ps[56] = new SqlParameter("@CostHandelsware", model.CostHandelsware);
            ps[57] = new SqlParameter("@CostProduktion", model.CostProduktion);
            ps[58] = new SqlParameter("@CostMontage", model.CostMontage);
            ps[59] = new SqlParameter("@CostAdministration", model.CostAdministration);
            ps[60] = new SqlParameter("@ExtraCostBIPercent", model.ExtraCostBIPercent);
            ps[61] = new SqlParameter("@ExtraCostBIFix", model.ExtraCostBIFix);
            ps[62] = new SqlParameter("@ExtraCostPrEPercent", model.ExtraCostPrEPercent);
            ps[63] = new SqlParameter("@ExtraCostPrEFix", model.ExtraCostPrEFix);
            ps[64] = new SqlParameter("@ExtraCostErfGar", model.ExtraCostErfGar);
            ps[65] = new SqlParameter("@ExtraCostHaftRuecklage", model.ExtraCostHaftRuecklage);
            ps[66] = new SqlParameter("@ExtraCostSkonto", model.ExtraCostSkonto);
            ps[67] = new SqlParameter("@ExtraCostProvisionDB", model.ExtraCostProvisionDB);
            ps[68] = new SqlParameter("@TimeProduktion", model.TimeProduktion);
            ps[69] = new SqlParameter("@TimeMontage", model.TimeMontage);
            ps[70] = new SqlParameter("@TimeAdministration", model.TimeAdministration);
            ps[72] = new SqlParameter("@TimeProducts", model.TimeProducts);

            ps[71] = new SqlParameter("@InvoiceToID", model.InvoiceRecipient);
            ps[73] = new SqlParameter("@Distance", model.Distance);

            return ps;
        }

        protected virtual SqlParameter[] GetParameters(BusinessCaseModel model, bool update) {

            SqlParameter[] ps = new SqlParameter[26];

            ps[0] = new SqlParameter("@ShortName", model.ShortName);

            ps[1] = new SqlParameter("@ErloesSuHw", model.ErloesSuHandelsware);
            ps[2] = new SqlParameter("@ErloesSuPr", model.ErloesSuProduktion);
            ps[3] = new SqlParameter("@ErloesSuMo", model.ErloesSuMontage);
            ps[4] = new SqlParameter("@ErloesRaPr", model.ErloesRaPr);
            ps[5] = new SqlParameter("@ErloesRaLe", model.ErloesRaLe);
            ps[6] = new SqlParameter("@ErloesRaPro", model.ErloesRaPro);
            ps[7] = new SqlParameter("@ErloesSf", model.ErloesSf);
            ps[8] = new SqlParameter("@ErloesEm", model.ErloesEm);
            ps[9] = new SqlParameter("@ErloesFa", model.ErloesFa);
            ps[10] = new SqlParameter("@CostHandelsware", model.CostHandelsware);
            ps[11] = new SqlParameter("@CostProduktion", model.CostProduktion);
            ps[12] = new SqlParameter("@CostMontage", model.CostMontage);
            ps[13] = new SqlParameter("@CostAdministration", model.CostAdministration);
            ps[14] = new SqlParameter("@ExtraCostBIPercent", model.ExtraCostBIPercent);
            ps[15] = new SqlParameter("@ExtraCostBIFix", model.ExtraCostBIFix);
            ps[16] = new SqlParameter("@ExtraCostPrEPercent", model.ExtraCostPrEPercent);
            ps[17] = new SqlParameter("@ExtraCostPrEFix", model.ExtraCostPrEFix);
            ps[18] = new SqlParameter("@ExtraCostErfGar", model.ExtraCostErfGar);
            ps[19] = new SqlParameter("@ExtraCostHaftRuecklage", model.ExtraCostHaftRuecklage);
            ps[20] = new SqlParameter("@ExtraCostSkonto", model.ExtraCostSkonto);
            ps[21] = new SqlParameter("@ExtraCostProvisionDB", model.ExtraCostProvisionDB);
            ps[22] = new SqlParameter("@TimeProduktion", model.TimeProduktion);
            ps[23] = new SqlParameter("@TimeMontage", model.TimeMontage);
            ps[24] = new SqlParameter("@TimeAdministration", model.TimeAdministration);
            ps[25] = new SqlParameter("@TimeProducts", model.TimeProducts);

            return ps;
        }

        #endregion

    }
}
